package TestSystemUtil;

import java.sql.*;
import java.util.ArrayList;

import TestSystemServer.Server;
import TestSystemServer.ServerThread;

public class DBUtil {
	public static final int ERR_IDNOTEXIST = 1;
	public static final int ERR_PWDISWRONG = 2;
	public static final int ERR_IDEXISTED = 3;
	public static final int ERR_QIDNOTEXIST = 4;
	public static final int ERR_NOHIGHSCORE = 5;
	public static final int SUCCESS = 0;
	
	public static int checkIDValid(String username, String password, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setString(1, username);
		Server.rs = Server.pst.executeQuery();
		if(!Server.rs.next()) return ERR_IDNOTEXIST; 
		else {
			if(!Server.rs.getString(3).equals(password)) return ERR_PWDISWRONG;
		}
		return SUCCESS;
	}
	public static int checkIDExist(String username, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setString(1, username);
		Server.rs = Server.pst.executeQuery();
		if(Server.rs.next()) return ERR_IDEXISTED;
		else return SUCCESS;
	}
	
	public static void registerNewID(String username, String password, String fullname, String course, String email, String phone, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setString(1, username);
		Server.pst.setString(2, password);
		Server.pst.setString(3, fullname);
		Server.pst.setString(4, course);
		Server.pst.setString(5, email);
		Server.pst.setString(6, phone);
		Server.pst.execute();
	}
	public static void insertIntoOnlineUsers(int uid, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, uid);
		Server.pst.execute();
	}
	// ====== KIỂM TRA MỘT QID CÓ TỒN TẠI TRONG DB KHÔNG ======//	
	public static int checkQIDExist(String qid, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setString(1, qid);
		Server.rs = Server.pst.executeQuery();
		if(!Server.rs.next()) return ERR_QIDNOTEXIST;
		else return SUCCESS;
	}
	// =======================================================//	
	
	public static void getHighScore(String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.rs = Server.pst.executeQuery();
	}
	
	public static void getHistory(String username, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setString(1, username);
		Server.rs = Server.pst.executeQuery();
	}
	
	public static void setOnline(String uid, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, Integer.parseInt(uid));
		Server.pst.execute();
	}
	public static void setOffline(String uid, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, Integer.parseInt(uid));
		Server.pst.execute();
	}
	
	public static ArrayList<String> getOnlineUsersID() throws SQLException {
		ArrayList<String> ids = new ArrayList<String>();
		String sql = "SELECT * FROM online_users WHERE online = 't'";
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		ResultSet rs = Server.pst.executeQuery();
		while(rs.next()) {
			ids.add(String.valueOf(rs.getInt(1)));
		}
		return ids;
	}
	public static String getUsernameFromID(int uid) throws SQLException {
		String sql = "SELECT * FROM users WHERE \"UID\" = ?";
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, uid);
		ResultSet rs = Server.pst.executeQuery();
		rs.next();
		return rs.getString(2);
	}
	
	public static void insertNewGroup(String hostname, String sql) throws SQLException {
		ServerThread hostThread = Server.getThread(hostname);
		int GID = hostThread.GID;
		int UID = Integer.parseInt(hostThread.uid);
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		System.out.println("Created new group " + GID);
		Server.pst.setInt(1, GID);
		Server.pst.setInt(2, UID);
		Server.pst.setInt(3, hostThread.score);
		Server.pst.execute();
	}
	
	public static void insertIntoExistedGroup(String hostname, String memName, String sql) throws SQLException {
		ServerThread hostThread = Server.getThread(hostname);
		ServerThread memThread = Server.getThread(memName);
		int GID = hostThread.GID;
		int UID = Integer.parseInt(memThread.uid);
		System.out.println("Added " + memName + " into group " + GID);
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, GID);
		Server.pst.setInt(2, UID);
		Server.pst.setInt(3, memThread.score);
		Server.pst.execute();
	}
	
	public static void removeMemberOfGroup(String hostname, String memName, String sql) throws SQLException {
		ServerThread hostThread = Server.getThread(hostname);
		ServerThread memThread = Server.getThread(memName);
		int GID = hostThread.GID;
		int UID = Integer.parseInt(memThread.uid);
		System.out.println("Remove " + memName + " from group " + GID);
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, GID);
		Server.pst.setInt(2, UID);
		Server.pst.execute();
	}
	
	public static int getScoreInGroup(String hostname, String memName) throws SQLException {
		String sql = "SELECT score FROM group_score WHERE \"GID\" = ? AND \"UID\" = ?";
		ServerThread hostThread = Server.getThread(hostname);
		ServerThread memThread = Server.getThread(memName);
		int GID = hostThread.GID;
		int UID = Integer.parseInt(memThread.uid);
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, GID);
		Server.pst.setInt(2, UID);
		Server.rs = Server.pst.executeQuery();
		Server.rs.next();
		return Server.rs.getInt(1);
	}
	
	public static void updateScoreInGroup(String hostname, String memName, int score) throws SQLException {
		String sql = "UPDATE group_score SET score = ? WHERE \"GID\" = ? AND \"UID\" = ?";
		ServerThread hostThread = Server.getThread(hostname);
		ServerThread memThread = Server.getThread(memName);
		int GID = hostThread.GID;
		int UID = Integer.parseInt(memThread.uid);
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, score);
		Server.pst.setInt(2, GID);
		Server.pst.setInt(3, UID);
		Server.pst.execute();
	}
	public static int getIDFromUsername (String username) throws SQLException {
		String sql = "SELECT * from users WHERE username = ?";
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setString(1,username);
		ResultSet rs = Server.pst.executeQuery();
		rs.next();
		return rs.getInt(1);
	}
	public static int getPlayTurnID () throws SQLException {
		String sql = "select count (\"PTID\") from play";
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		ResultSet rs = Server.pst.executeQuery();
		rs.next();
		int PTID = rs.getInt(1) + 1;
		return PTID;
	}
	
	public static int getGroupID () throws SQLException {
		String sql = "select count (distinct \"GID\") from group_score";
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		ResultSet rs = Server.pst.executeQuery();
		rs.next();
		int GID = rs.getInt(1)+1;
		return GID;
	}
	
	public static void setQuestionSet(int ptid) throws SQLException {
		String sql = "insert into questionset values (?)";
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, ptid);
		Server.pst.execute();
	}
	
	public static void setScore(int id, int ptid, String ans, String score, String sql) throws SQLException {
		Server.pst = Server.sqlconnect.prepareStatement(sql);
		Server.pst.setInt(1, id);
		Server.pst.setInt(2, ptid);
		java.sql.Date sqldate = new java.sql.Date(new java.util.Date().getTime());
        Server.pst.setDate(3, sqldate);
		Server.pst.setInt(4, Integer.parseInt(ans));
		Server.pst.setInt(5, Integer.parseInt(score));
		Server.pst.execute();
	}
	
	
}
